package db.tables;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import control.ConnectionManage;
import db.parking.beans.Complaints;

/**
 * AdminComplaints manages all requests From,To table Complaints
 * @author Operations
 *
 */
public class AdminComplaints {

	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();

	//return array list
	/**
	 * Get all complaints from database into tables from DB table complaints
	 * @param limit
	 * @param order
	 * @return
	 * @throws SQLException
	 */
	public static ArrayList<Complaints> getAll(int limit,String order) throws SQLException {
		ArrayList<Complaints> result=new ArrayList<Complaints>();
			
		String sql = "SELECT *,UNIX_TIMESTAMP(timestamp) AS DATE FROM complaints";
		if(!order.equals(null)){
             sql+=" order by "+order;
		}
      	sql+=" limit ?";
		ResultSet rs = null;
        
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setInt(1, limit);
			rs = stmt.executeQuery();
				
			while(rs.next()) {				
				Complaints bean = new Complaints();
				bean.setComplaintID(rs.getInt("complaintID"));
				bean.setTimestamp(rs.getTimestamp("timestamp"));
				bean.setCustomerID(rs.getString("customerID"));
				bean.setDescription(rs.getString("description"));
				bean.setStatus(rs.getInt("status"));
				bean.setOrderID(rs.getInt("orderID"));
				bean.setAmount(rs.getInt("amount"));
				bean.setTitle(rs.getString("title"));
				result.add(bean);
			}
			return result;	

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	
	
	/**
	 * Get row by id from database table Complaints
	 * @param complaintId
	 * @return
	 * @throws SQLException
	 */
	public static Complaints getRow(int complaintId) throws SQLException {

		String sql = "SELECT * FROM Complaints WHERE complaintID = ?";
		ResultSet rs = null;

		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setInt(1, complaintId);
			rs = stmt.executeQuery();

			if (rs.next()) {
				Complaints bean = new Complaints();
				bean.setComplaintID(complaintId);
				bean.setTimestamp(rs.getTimestamp("timestamp"));
				bean.setCustomerID(rs.getString("customerID"));
				bean.setDescription(rs.getString("description"));
				bean.setStatus(rs.getInt("status"));
				bean.setOrderID(rs.getInt("orderID"));
				bean.setAmount(rs.getInt("amount"));
				bean.setTitle(rs.getString("title"));

				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	/**
	 * Insert a new complaint into database table complaints
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static boolean insert(Complaints bean) throws Exception {
		// complaintID is autoIncremented on Insert
		String sql = "INSERT into Complaints (timestamp , customerID , title , description , status , orderID , amount) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?)";
		ResultSet keys = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {

			stmt.setTimestamp(1, bean.getTimestamp());
			stmt.setString(2, bean.getCustomerID());
			stmt.setString(3, bean.getTitle());
			stmt.setString(4, bean.getDescription());
			stmt.setInt(5, bean.getStatus());
			stmt.setInt(6, bean.getOrderID());
			stmt.setFloat(7, bean.getAmount());
			int affected = stmt.executeUpdate();

			if (affected == 1) {
				keys = stmt.getGeneratedKeys();
				keys.next();
				int newKey = keys.getInt(1);
				bean.setComplaintID(newKey);
			} else {
				System.err.println("No rows affected");
				return false;
			}

		} catch (SQLException e) {
			System.err.println(e + "AdminComplaint");
			return false;
		} finally {
			if (keys != null)
				keys.close();
		}
		return true;

	}
	
	
	// update complaint status
		/**
		 * Update the amount that is needed to be refunded to customer in table complaints
		 * @param bean
		 * @return
		 * @throws SQLException
		 */
		public static boolean updateAmount(Complaints bean) throws SQLException {
	       
			String sql = "UPDATE Complaints SET " + "amount = ? " + "WHERE complaintID = ?";
			try (PreparedStatement stmt = conn.prepareStatement(sql);) {

				stmt.setFloat(1, bean.getAmount());
				stmt.setInt(2, bean.getComplaintID());

				int affected = stmt.executeUpdate();
				if (affected == 1) {
					return true;
				} else {
					return false;
				}

			} catch (SQLException e) {
				System.err.println(e);
				return false;
			}

		}

	// update complaint status
	/**
	 * Update row status by id in DB table complaints
	 * @param bean
	 * @return
	 * @throws SQLException
	 */
	public static boolean update(Complaints bean) throws SQLException {
       
		String sql = "UPDATE Complaints SET " + "status = ? " + "WHERE complaintID = ?";
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {

			stmt.setInt(1, bean.getStatus());
			stmt.setInt(2, bean.getComplaintID());

			int affected = stmt.executeUpdate();
			if (affected == 1) {
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return false;
		}

	}

	// delete complaint by id
	/**
	 * Delete row from DB table complaints by id
	 * @param complaintId
	 * @return
	 * @throws Exception
	 */
	public static boolean delete(int complaintId) throws Exception {

		String sql = "DELETE FROM Complaints WHERE complaintID = ?";
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {

			stmt.setInt(1, complaintId);
			int affected = stmt.executeUpdate();
			if (affected == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	
	
	//return all complaints in all parking lots
	/**
	 * Get all complaints into an array from DB table complaints
	 * @return
	 * @throws SQLException
	 */
	public static Complaints[] getAllComplaints() throws SQLException {
		String sql = "SELECT * FROM complaints ORDER BY status";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Complaints bean[] = new Complaints[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Complaints();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setComplaintID(rs.getInt("complaintID"));
				bean[rowCount].setTimestamp(rs.getTimestamp("timestamp"));
				bean[rowCount].setCustomerID(rs.getString("customerID"));
				bean[rowCount].setDescription(rs.getString("description"));
				bean[rowCount].setStatus(rs.getInt("status"));
				bean[rowCount].setOrderID(rs.getInt("orderID"));
				bean[rowCount].setAmount(rs.getInt("amount"));
				bean[rowCount].setTitle(rs.getString("title"));
				rowCount++;
			}
			return bean;
		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}



	/**
	 * Get Specific Complaint rows by interval from DB table complaints
	 * @param startTime
	 * @param leaveTime
	 * @param lotNo
	 * @return
	 * @throws SQLException
	 */
	public static Complaints[] getComplaintDuringTimeBylotNo(Timestamp startTime, Timestamp leaveTime, int lotNo) throws SQLException {
		String sql = "SELECT * FROM complaints WHERE timestamp >=? AND timestamp <?  ";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, startTime);
			stmt.setTimestamp(2, leaveTime);
			
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Complaints bean[] = new Complaints[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Complaints();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setComplaintID(rs.getInt("complaintID"));
				bean[rowCount].setTimestamp(rs.getTimestamp("timestamp"));
				bean[rowCount].setCustomerID(rs.getString("customerID"));
				bean[rowCount].setDescription(rs.getString("description"));
				bean[rowCount].setStatus(rs.getInt("status"));
				bean[rowCount].setOrderID(rs.getInt("orderID"));
				bean[rowCount].setAmount(rs.getInt("amount"));
				bean[rowCount].setTitle(rs.getString("title"));
				rowCount++;
			}
			return bean;
		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}

}
